package org.clever.sharding.jdbc.service;

import io.shardingsphere.api.HintManager;
import io.shardingsphere.shardingjdbc.jdbc.core.statement.MasterSlavePreparedStatement;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.clever.sharding.jdbc.entity.UserRole;
import org.clever.sharding.jdbc.mapper.RolePermissionMapper;
import org.clever.sharding.jdbc.mapper.UserRoleMapper;
import org.clever.sharding.jdbc.mapper.UserSysMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 作者： lzw<br/>
 * 创建时间：2018-11-09 15:54 <br/>
 */
@Transactional(readOnly = true)
@Service
@Slf4j
public class TestService {

    @Autowired
    private RolePermissionMapper rolePermissionMapper;
    @Autowired
    private UserRoleMapper userRoleMapper;
    @Autowired
    private UserSysMapper userSysMapper;

    /**
     * 只读
     */
    public Object read() {
        return rolePermissionMapper.get("read", "read");
    }

    /**
     * 只写
     */
    @Transactional
    public Object write() {
        UserRole userRole = new UserRole();
        userRole.setRoleName("write");
        userRole.setUsername("write");
        return userRoleMapper.insert(userRole);
    }

    /**
     * 先写在读
     */
    @Transactional
    public Object writeAndRead() {
        // 写主库
        UserRole userRole = new UserRole();
        userRole.setRoleName("write");
        userRole.setUsername("write");
        log.info("### 写 {}", userRoleMapper.insert(userRole));
        // 读主库
        return userSysMapper.selectList(null);
    }

    /**
     * 先读在写
     */
    @Transactional
    public Object readAndWrite() {
        // 读从库
        log.info("### 读 {}", userSysMapper.selectList(null));
        UserRole userRole = new UserRole();
        userRole.setRoleName("write");
        userRole.setUsername("write");
        // 写主库
        return userRoleMapper.insert(userRole);
    }

    /**
     * 写 - 读 - 写
     */
    @Transactional
    public Object writeAndReadAndWrite() {
        // 写主库
        UserRole userRole = new UserRole();
        userRole.setRoleName("write");
        userRole.setUsername("write");
        log.info("### 写 {}", userRoleMapper.insert(userRole));
        // 读主库
        log.info("### 读 {}", userSysMapper.selectList(null));
        // 写主库
        userRole = new UserRole();
        userRole.setRoleName("write");
        userRole.setUsername("write");
        return userRoleMapper.insert(userRole);
    }

    /**
     * 写 - 读 - 读 - 写
     */
    @Transactional
    public Object writeAndReadsAndWrite() {
        // 写主库
        UserRole userRole = new UserRole();
        userRole.setRoleName("write");
        userRole.setUsername("write");
        log.info("### 写 {}", userRoleMapper.insert(userRole));
        // 读主库
        log.info("### 读 {}", userSysMapper.selectList(null));
        log.info("### 读 {}", userSysMapper.selectList(null));
        // 写主库
        userRole = new UserRole();
        userRole.setRoleName("write");
        userRole.setUsername("write");
        return userRoleMapper.insert(userRole);
    }

    /**
     * 读 - 写 - 读
     */
    @Transactional
    public Object readAndWriteAndRead() {
        // 读从库
        log.info("### 读 {}", userSysMapper.selectList(null));
        UserRole userRole = new UserRole();
        userRole.setRoleName("write");
        userRole.setUsername("write");
        // 写主库
        log.info("### 写 {}", userRoleMapper.insert(userRole));
        return userSysMapper.selectList(null);
    }

    /**
     * 读 - 写 - 写 - 读
     */
    @Transactional
    public Object readAndWritesAndRead() {
        // 读从库
        log.info("### 读 {}", userSysMapper.selectList(null));
        UserRole userRole = new UserRole();
        userRole.setRoleName("write");
        userRole.setUsername("write");
        // 写主库
        log.info("### 写 {}", userRoleMapper.insert(userRole));
        log.info("### 写 {}", userRoleMapper.insert(userRole));
        return userSysMapper.selectList(null);
    }

    /**
     * 强制在写库读
     */
    public Object forceReadInWrite() {
        HintManager.getInstance().setMasterRouteOnly();
        log.info("### 读 {}", userSysMapper.selectList(null));
        log.info("### 读 {}", userSysMapper.selectList(null));
        return userSysMapper.selectList(null);
    }

    /**
     * 在@Select中insert
     */
    @Transactional
    public Object insertBySelect() {
        UserRole userRole = new UserRole();
        userRole.setRoleName("write");
        userRole.setUsername("write");
        Integer count = userRoleMapper.insertBySelect(userRole);
        log.info("### 在@Select中insert -> {}", count);
        return count;
    }

    @Transactional
    public Object insertBySelect2() {
        UserRole userRole = new UserRole();
        userRole.setRoleName("write");
        userRole.setUsername("write");
        userRoleMapper.insertBySelect2(userRole);
        log.info("### 在@Select中insert -> null");
        return null;
    }

    @Transactional
    public Object nativeSql() {
        UserRole userRole = new UserRole();
        userRole.setRoleName("write");
        userRole.setUsername("write");
        userRoleMapper.nativeSql(userRole);
        log.info("### nativeSql -> null");
        return null;
    }

    @Autowired
    private DataSource dataSource;
    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    @Transactional
    public Object test() {
//        SqlSession sqlSession = sqlSessionFactory.openSession();
//        log.info("### sqlSession => {}", sqlSession);
        log.info("### dataSource => {}", dataSource);
        try {
            Connection connection = dataSource.getConnection();
            connection.setAutoCommit(true);
            String sql = "INSERT INTO `user_role`(`username`, `role_name`) VALUES (?, ?)";
            try {
                PreparedStatement statement = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
                statement.setString(1, "write");
                statement.setString(2, "write");
                statement.execute();
                ResultSet rs = statement.getResultSet();
                int i = 0;
                while (rs == null) {
                    i++;
                    if (i > 10) {
                        break;
                    }
                    // move forward to get the first resultset in case the driver
                    // doesn't return the resultset as the first result (HSQLDB 2.1)
                    boolean flag;
                    if (statement instanceof MasterSlavePreparedStatement) {
                        MasterSlavePreparedStatement masterSlavePreparedStatement = (MasterSlavePreparedStatement) statement;
                        flag = masterSlavePreparedStatement.getRoutedStatements().iterator().next().getMoreResults();
                    } else {
                        flag = statement.getMoreResults();
                    }
                    if (flag) {
                        rs = statement.getResultSet();
                    } else {
                        int count = statement.getUpdateCount();
                        log.info("### count => {}", count);
                        if (count == -1) {
                            // no more results. Must be no resultset
                            break;
                        }
                    }
                }
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
